<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>webdoc</title>
    <link rel="stylesheet" type="text/css" href="../static/css/main.css">
</head>
<body>
<div class="nav">
    <div class="logo">
        
            webdoc
        
    </div>
<ul><li><a href="../index.html">0.Async</a></li><li><a href="../html/0.editor.html">0.editor</a></li><li><a href="../html/0.module.html">0.module</a></li><li><a href="../html/1.ES2015.html">1.ES2015</a></li><li><a href="../html/2.Promise.html">2.Promise</a></li><li><a href="../html/3.Node.html">3.Node</a></li><li><a href="../html/4.NodeInstall.html">4.NodeInstall</a></li><li><a href="../html/5.REPL.html">5.REPL</a></li><li><a href="../html/6.NodeCore.html">6.NodeCore</a></li><li><a href="../html/7.module&NPM.html">7.module&NPM</a></li><li><a href="../html/8.Encoding.html">8.Encoding</a></li><li><a href="../html/9.Buffer.html">9.Buffer</a></li><li><a href="../html/10.fs.html">10.fs</a></li><li><a href="../html/11.Stream-1.html">11.Stream-1</a></li><li><a href="../html/11.Stream-2.html">11.Stream-2</a></li><li><a href="../html/11.Stream-3.html">11.Stream-3</a></li><li><a href="../html/11.Stream-4.html">11.Stream-4</a></li><li><a href="../html/12-Network-2.html">12-Network-2</a></li><li><a href="../html/12.NetWork-3.html">12.NetWork-3</a></li><li><a href="../html/12.Network-1.html">12.Network-1</a></li><li><a href="../html/13.tcp.html">13.tcp</a></li><li><a href="../html/14.http-1.html">14.http-1</a></li><li><a href="../html/14.http-2.html">14.http-2</a></li><li><a href="../html/15.compress.html">15.compress</a></li><li><a href="../html/16.crypto.html">16.crypto</a></li><li><a href="../html/17.process.html">17.process</a></li><li><a href="../html/18.yargs.html">18.yargs</a></li><li><a href="../html/19.cache.html">19.cache</a></li><li><a href="../html/20.action.html">20.action</a></li><li><a href="../html/21.https.html">21.https</a></li><li><a href="../html/22.cookie.html">22.cookie</a></li><li><a href="../html/23.session.html">23.session</a></li><li><a href="../html/24.express-1.html">24.express-1</a></li><li><a href="../html/24.express-2.html">24.express-2</a></li><li><a href="../html/24.express-3.html">24.express-3</a></li><li><a href="../html/24.express-4.html">24.express-4</a></li><li><a href="../html/25.koa-1.html">25.koa-1</a></li><li><a href="../html/26.webpack-1-basic.html">26.webpack-1-basic</a></li><li><a href="../html/26.webpack-2-optimize.html">26.webpack-2-optimize</a></li><li><a href="../html/26.webpack-3.tapable.html">26.webpack-3.tapable</a></li><li><a href="../html/26.webpack-4-AST.html">26.webpack-4-AST</a></li><li><a href="../html/26.webpack-5-source.html">26.webpack-5-source</a></li><li><a href="../html/26.webpack-6-loader.html">26.webpack-6-loader</a></li><li><a href="../html/26.webpack-7-plugin.html">26.webpack-7-plugin</a></li><li><a href="../html/26.webpack-8-hand.html">26.webpack-8-hand</a></li><li><a href="../html/27.react-1.html">27.react-1</a></li><li><a href="../html/27.react-2.html">27.react-2</a></li><li><a href="../html/27.react-3.html">27.react-3</a></li><li><a href="../html/27.react-4-immutable.html">27.react-4-immutable</a></li><li><a href="../html/27.react-5-react-dom-diff.html">27.react-5-react-dom-diff</a></li><li><a href="../html/27.react-6.html">27.react-6</a></li><li><a href="../html/28.react-mobx.html">28.react-mobx</a></li><li><a href="../html/28.redux-0.html">28.redux-0</a></li><li><a href="../html/28.redux-1.html">28.redux-1</a></li><li><a href="../html/28.redux-2-中间件.html">28.redux-2-中间件</a></li><li><a href="../html/28.redux-3-saga.html">28.redux-3-saga</a></li><li><a href="../html/28.redux-jwt-back.html">28.redux-jwt-back</a></li><li><a href="../html/28.redux-jwt-front.html">28.redux-jwt-front</a></li><li><a href="../html/29.mongodb-1.html">29.mongodb-1</a></li><li><a href="../html/29.mongodb-2.html">29.mongodb-2</a></li><li><a href="../html/29.mongodb-3.html">29.mongodb-3</a></li><li><a href="../html/29.mongodb-4.html">29.mongodb-4</a></li><li><a href="../html/29.mongodb-5.html">29.mongodb-5</a></li><li><a href="../html/29.mongodb-6.html">29.mongodb-6</a></li><li><a href="../html/30.cms-1-mysql.html">30.cms-1-mysql</a></li><li class="active"><a href="../html/30.cms-2-mysql.html">30.cms-2-mysql</a></li><li><a href="../html/30.cms-3-mysql.html">30.cms-3-mysql</a></li><li><a href="../html/30.cms-4-egg.html">30.cms-4-egg</a></li><li><a href="../html/30.cms-5-api.html">30.cms-5-api</a></li><li><a href="../html/30.cms-6-roadhog.html">30.cms-6-roadhog</a></li><li><a href="../html/30.cms-7-umi.html">30.cms-7-umi</a></li><li><a href="../html/30.cms-8-dva.html">30.cms-8-dva</a></li><li><a href="../html/30.cms-9-dva.html">30.cms-9-dva</a></li><li><a href="../html/30.cms-10-dva.html">30.cms-10-dva</a></li><li><a href="../html/30.cms-11-front.html">30.cms-11-front</a></li><li><a href="../html/31.cms-12-api.html">31.cms-12-api</a></li><li><a href="../html/31.cms-13-front.html">31.cms-13-front</a></li><li><a href="../html/31.cms-14-deploy.html">31.cms-14-deploy</a></li><li><a href="../html/32.ant.html">32.ant</a></li><li><a href="../html/33.redis.html">33.redis</a></li><li><a href="../html/34.unittest.html">34.unittest</a></li><li><a href="../html/35.jwt.html">35.jwt</a></li><li><a href="../html/36.websocket-1.html">36.websocket-1</a></li><li><a href="../html/36.websocket-2.html">36.websocket-2</a></li><li><a href="../html/38.chat-api-1.html">38.chat-api-1</a></li><li><a href="../html/38.chat-api-2.html">38.chat-api-2</a></li><li><a href="../html/38.chat-3.html">38.chat-3</a></li><li><a href="../html/38.chat-api-3.html">38.chat-api-3</a></li><li><a href="../html/38.chat.html">38.chat</a></li><li><a href="../html/38.chat2.html">38.chat2</a></li><li><a href="../html/38.chat2.html">38.chat2</a></li><li><a href="../html/39.crawl-0.html">39.crawl-0</a></li><li><a href="../html/39.crawl-1.html">39.crawl-1</a></li><li><a href="../html/39.crawl-2.html">39.crawl-2</a></li><li><a href="../html/40.deploy.html">40.deploy</a></li><li><a href="../html/41.safe.html">41.safe</a></li><li><a href="../html/42.test.html">42.test</a></li><li><a href="../html/43.nginx.html">43.nginx</a></li><li><a href="../html/44.enzyme.html">44.enzyme</a></li><li><a href="../html/45.docker.html">45.docker</a></li><li><a href="../html/46.elastic.html">46.elastic</a></li><li><a href="../html/47.oauth.html">47.oauth</a></li><li><a href="../html/48.wxpay.html">48.wxpay</a></li><li><a href="../html/49.nunjucks.html">49.nunjucks</a></li><li><a href="../html/50.ketang.html">50.ketang</a></li><li><a href="../html/index.html">index</a></li><li><a href="../html/51.typescript.html">51.typescript</a></li><li><a href="../html/52.UML.html">52.UML</a></li><li><a href="../html/53.design.html">53.design</a></li><li><a href="../html/index.html">index</a></li></ul></div>


<div class="warpper">

    <div class="page-toc">
        <ul><li><a href="#t01. 什么是聚合函数">1. 什么是聚合函数</a><ul><li><a href="#t11.1 SUM">1.1 SUM</a></li><li><a href="#t21.2 AVG">1.2 AVG</a></li><li><a href="#t31.3 MAX、MIN">1.3 MAX、MIN</a></li><li><a href="#t41.4 AVG">1.4 AVG</a></li><li><a href="#t51.5 COUNT">1.5 COUNT</a></li></ul></li><li><a href="#t62. 分组">2. 分组</a><ul><li><a href="#t72.1 语法">2.1 语法</a></li><li><a href="#t82.2 练习">2.2 练习</a></li><li><a href="#t92.3 分组筛选">2.3 分组筛选</a><ul><li><a href="#t102.3.1 语法">2.3.1 语法</a></li><li><a href="#t112.3.2 练习">2.3.2 练习</a></li></ul></li></ul></li><li><a href="#t123. 子查询">3. 子查询</a><ul><li><a href="#t133.1  比较运算符的子查询">3.1  比较运算符的子查询</a></li><li><a href="#t143.2 查询年龄大于平均年龄的学生">3.2 查询年龄大于平均年龄的学生</a></li><li><a href="#t153.2 ANY SOME ALL">3.2 ANY SOME ALL</a></li><li><a href="#t163.3 查询一下有考试成绩的学生信息">3.3 查询一下有考试成绩的学生信息</a></li></ul></li><li><a href="#t174. 表连接">4. 表连接</a><ul><li><a href="#t18４.1　连接类型">４.1　连接类型</a></li><li><a href="#t19４.2　连接条件">４.2　连接条件</a></li><li><a href="#t20４.3　内连接">４.3　内连接</a></li><li><a href="#t21４.4　左外连接">４.4　左外连接</a></li><li><a href="#t22４.5　右外连接">４.5　右外连接</a></li><li><a href="#t23４.6　更多表连接">４.6　更多表连接</a></li><li><a href="#t24４.7　无限分类[自身连接]">４.7　无限分类[自身连接]</a></li><li><a href="#t25４.8 删除重复记录[多表删除]">４.8 删除重复记录[多表删除]</a><ul><li><a href="#t26４.8.1 多表联合删除重复记录">４.8.1 多表联合删除重复记录</a></li><li><a href="#t27４.8.2 IN NOT IN删除重复记录">４.8.2 IN NOT IN删除重复记录</a></li></ul></li><li><a href="#t284.9 (插入省份)INSERT SELECT">4.9 (插入省份)INSERT SELECT</a></li><li><a href="#t29４.10　更新省份(多表更新)">４.10　更新省份(多表更新)</a></li><li><a href="#t30４.11　多表联合查询">４.11　多表联合查询</a></li><li><a href="#t31４.12 CREATE SELECT">４.12 CREATE SELECT</a></li><li><a href="#t32４.13 修改列名和类型">４.13 修改列名和类型</a></li></ul></li></ul>
    </div>
    
    <div class="content markdown-body">
        <h2 id="t01. &#x4EC0;&#x4E48;&#x662F;&#x805A;&#x5408;&#x51FD;&#x6570;">1. &#x4EC0;&#x4E48;&#x662F;&#x805A;&#x5408;&#x51FD;&#x6570; <a href="#t01. &#x4EC0;&#x4E48;&#x662F;&#x805A;&#x5408;&#x51FD;&#x6570;"> # </a></h2>
<p>&#x5BF9;&#x4E00;&#x7EC4;&#x503C;&#x8FDB;&#x884C;&#x8BA1;&#x7B97;&#xFF0C;&#x5E76;&#x8FD4;&#x56DE;&#x8BA1;&#x7B97;&#x540E;&#x7684;&#x503C;&#xFF0C;&#x4E00;&#x822C;&#x7528;&#x6765;&#x7EDF;&#x8BA1;&#x6570;&#x636E;</p>
<h3 id="t11.1 SUM">1.1 SUM <a href="#t11.1 SUM"> # </a></h3>
<p>&#x7D2F;&#x52A0;&#x6240;&#x6709;&#x884C;&#x7684;&#x503C;</p>
<pre><code class="lang-sql">&#x8BA1;&#x7B97;ID=1&#x7684;&#x5B66;&#x751F;&#x7684;&#x7684;&#x603B;&#x5206;
<span class="hljs-keyword">select</span> <span class="hljs-keyword">SUM</span>(grade)  <span class="hljs-keyword">as</span> <span class="hljs-string">&apos;&#x603B;&#x5206;&apos;</span> <span class="hljs-keyword">from</span> score <span class="hljs-keyword">where</span> student_id = <span class="hljs-number">1</span>;
</code></pre>
<h3 id="t21.2 AVG">1.2 AVG <a href="#t21.2 AVG"> # </a></h3>
<p>&#x8BA1;&#x7B97;&#x6240;&#x6709;&#x884C;&#x7684;&#x5E73;&#x5747;&#x503C;</p>
<pre><code class="lang-sql">&#x8BA1;&#x7B97;ID=1&#x7684;&#x5B66;&#x751F;&#x7684;&#x7684;&#x5E73;&#x5747;&#x5206;
<span class="hljs-keyword">select</span> <span class="hljs-keyword">AVG</span>(grade) <span class="hljs-keyword">as</span> <span class="hljs-string">&apos;&#x5E73;&#x5747;&#x5206;&apos;</span> <span class="hljs-keyword">from</span> score <span class="hljs-keyword">where</span> student_id = <span class="hljs-number">1</span>;
</code></pre>
<h3 id="t31.3 MAX&#x3001;MIN">1.3 MAX&#x3001;MIN <a href="#t31.3 MAX&#x3001;MIN"> # </a></h3>
<p>&#x8BA1;&#x7B97;&#x6240;&#x6709;&#x884C;&#x7684;&#x5E73;&#x5747;&#x503C;</p>
<pre><code class="lang-sql">&#x8BA1;&#x7B97;ID=1&#x7684;&#x5B66;&#x751F;&#x7684;&#x7684;&#x5E73;&#x5747;&#x5206;
<span class="hljs-keyword">select</span> <span class="hljs-keyword">AVG</span>(grade) <span class="hljs-keyword">as</span> <span class="hljs-string">&apos;&#x5E73;&#x5747;&#x5206;&apos;</span>,<span class="hljs-keyword">MAX</span>(grade) &#x6700;&#x9AD8;&#x5206;,<span class="hljs-keyword">MIN</span>(grade) &#x6700;&#x4F4E;&#x5206; <span class="hljs-keyword">from</span> score <span class="hljs-keyword">where</span> student_id = <span class="hljs-number">1</span>;
</code></pre>
<h3 id="t41.4 AVG">1.4 AVG <a href="#t41.4 AVG"> # </a></h3>
<p>&#x8BA1;&#x7B97;&#x6240;&#x6709;&#x884C;&#x7684;&#x5E73;&#x5747;&#x503C;</p>
<pre><code class="lang-sql"><span class="hljs-keyword">select</span> <span class="hljs-keyword">AVG</span>(grade) <span class="hljs-keyword">as</span> <span class="hljs-string">&apos;&#x5E73;&#x5747;&#x5206;&apos;</span>,<span class="hljs-keyword">MAX</span>(grade) &#x6700;&#x9AD8;&#x5206;,<span class="hljs-keyword">MIN</span>(grade) &#x6700;&#x4F4E;&#x5206; <span class="hljs-keyword">from</span> score <span class="hljs-keyword">where</span> student_id = <span class="hljs-number">1</span>;
</code></pre>
<h3 id="t51.5 COUNT">1.5 COUNT <a href="#t51.5 COUNT"> # </a></h3>
<p>&#x8BA1;&#x7B97;&#x5B66;&#x751F;&#x603B;&#x6570;</p>
<pre><code class="lang-sql"><span class="hljs-keyword">select</span> <span class="hljs-keyword">COUNT</span>(*) <span class="hljs-keyword">from</span> student;
</code></pre>
<h2 id="t62. &#x5206;&#x7EC4;">2. &#x5206;&#x7EC4; <a href="#t62. &#x5206;&#x7EC4;"> # </a></h2>
<p>&#x5206;&#x7EC4;&#x67E5;&#x8BE2;&#x5C31;&#x662F;&#x6309;&#x67D0;&#x5217;&#x7684;&#x503C;&#x8FDB;&#x884C;&#x5206;&#x7EC4;&#xFF0C;&#x76F8;&#x540C;&#x7684;&#x503C;&#x5206;&#x6210;&#x4E00;&#x7EC4;&#xFF0C;&#x7136;&#x540E;&#x53EF;&#x4EE5;&#x5BF9;&#x6B64;&#x7EC4;&#x5185;&#x8FDB;&#x884C;&#x6C42;&#x5E73;&#x5747;&#x3001;&#x6C42;&#x548C;&#x7B49;&#x8BA1;&#x7B97;</p>
<p><img src="http://img.zhufengpeixun.cn/splitgroup.png" alt="splitgroup"></p>
<h3 id="t72.1 &#x8BED;&#x6CD5;">2.1 &#x8BED;&#x6CD5; <a href="#t72.1 &#x8BED;&#x6CD5;"> # </a></h3>
<pre><code class="lang-sql"><span class="hljs-keyword">SELECT</span> &#x5217;&#x540D;,&#x67E5;&#x8BE2;&#x8868;&#x8FBE;&#x5F0F;
<span class="hljs-keyword">FROM</span>  &lt;&#x8868;&#x540D;&gt;  
<span class="hljs-keyword">WHERE</span>  &lt;&#x6761;&#x4EF6;&gt;
<span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> &lt;&#x5206;&#x7EC4;&#x5B57;&#x6BB5;&gt;
<span class="hljs-keyword">HAVING</span> &#x5206;&#x7EC4;&#x540E;&#x7684;&#x8FC7;&#x6EE4;&#x6761;&#x4EF6;
<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> &#x5217;&#x540D; [<span class="hljs-keyword">ASC</span>,<span class="hljs-keyword">DESC</span>]
<span class="hljs-keyword">LIMIT</span> &#x504F;&#x79FB;&#x91CF;,&#x6761;&#x6570;
</code></pre>
<p>SELECT&#x5217;&#x8868;&#x4E2D;&#x53EA;&#x80FD;&#x5305;&#x542B;&#xFF1A;</p>
<ul>
<li>&#x88AB;&#x5206;&#x7EC4;&#x7684;&#x5217;</li>
<li>&#x4E3A;&#x6BCF;&#x4E2A;&#x5206;&#x7EC4;&#x8FD4;&#x56DE;&#x4E00;&#x4E2A;&#x503C;&#x7684;&#x8868;&#x8FBE;&#x5F0F;&#xFF0C;&#x5982;&#x805A;&#x5408;&#x51FD;&#x6570;</li>
</ul>
<h3 id="t82.2 &#x7EC3;&#x4E60;">2.2 &#x7EC3;&#x4E60; <a href="#t82.2 &#x7EC3;&#x4E60;"> # </a></h3>
<ul>
<li>&#x7EDF;&#x8BA1;&#x6BCF;&#x4F4D;&#x540C;&#x5B66;&#x7684;&#x5E73;&#x5747;&#x6210;&#x7EE9;-&#x5355;&#x5217;&#x5206;&#x7EC4;<pre><code class="lang-sql"><span class="hljs-keyword">select</span> student_id,<span class="hljs-keyword">avg</span>(grade) <span class="hljs-keyword">from</span> score <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> student_id;
</code></pre>
</li>
<li>&#x7EDF;&#x8BA1;&#x6BCF;&#x95E8;&#x8BFE;&#x7A0B;&#x7684;&#x6700;&#x9AD8;&#x5206;&#xFF0C;&#x5E76;&#x6309;&#x5206;&#x6570;&#x4ECE;&#x9AD8;&#x5230;&#x4F4E;&#x6392;&#x5217;<pre><code class="lang-sql"><span class="hljs-keyword">select</span> course_id,<span class="hljs-keyword">max</span>(grade) &#x5E73;&#x5747;&#x5206; <span class="hljs-keyword">from</span> score <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> course_id <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">max</span>(grade) <span class="hljs-keyword">desc</span>
</code></pre>
</li>
<li>&#x7EDF;&#x8BA1;&#x5404;&#x7701;&#x7684;&#x7537;&#x5973;&#x540C;&#x5B66;&#x4EBA;&#x6570;-&#x591A;&#x5217;&#x5206;&#x7EC4;<pre><code class="lang-sql"><span class="hljs-keyword">select</span> province,gender,<span class="hljs-keyword">COUNT</span>(*) <span class="hljs-keyword">from</span> student <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> province,gender
</code></pre>
<h3 id="t92.3 &#x5206;&#x7EC4;&#x7B5B;&#x9009;">2.3 &#x5206;&#x7EC4;&#x7B5B;&#x9009; <a href="#t92.3 &#x5206;&#x7EC4;&#x7B5B;&#x9009;"> # </a></h3>
</li>
</ul>
<h4 id="t102.3.1 &#x8BED;&#x6CD5;">2.3.1 &#x8BED;&#x6CD5; <a href="#t102.3.1 &#x8BED;&#x6CD5;"> # </a></h4>
<pre><code class="lang-sql"><span class="hljs-keyword">SELECT</span>  <span class="hljs-keyword">FROM</span>  &lt;&#x8868;&#x540D;&gt;
<span class="hljs-keyword">WHERE</span> 
<span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> {col_name|expr|<span class="hljs-keyword">position</span>}
<span class="hljs-keyword">HAVING</span>  {col_name|expr|<span class="hljs-keyword">position</span>}
<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> {col_name|expr|<span class="hljs-keyword">position</span>} [<span class="hljs-keyword">ASC</span>|<span class="hljs-keyword">DESC</span>]
<span class="hljs-keyword">LIMIT</span> <span class="hljs-keyword">offset</span>,<span class="hljs-keyword">row_count</span>
</code></pre>
<ol>
<li>WHERE&#x7528;&#x4E8E;&#x8FC7;&#x6EE4;&#x6389;&#x4E0D;&#x7B26;&#x5408;&#x6761;&#x4EF6;&#x7684;&#x8BB0;&#x5F55;</li>
<li>HAVING &#x7528;&#x4E8E;&#x8FC7;&#x6EE4;&#x5206;&#x7EC4;&#x540E;&#x7684;&#x8BB0;&#x5F55;</li>
<li>GROUP BY&#x7528;&#x4E8E;&#x5BF9;&#x7B5B;&#x9009;&#x540E;&#x7684;&#x7ED3;&#x679C;&#x8FDB;&#x884C;&#x5206;&#x7EC4;</li>
</ol>
<h4 id="t112.3.2 &#x7EC3;&#x4E60;">2.3.2 &#x7EC3;&#x4E60; <a href="#t112.3.2 &#x7EC3;&#x4E60;"> # </a></h4>
<ul>
<li>&#x7EDF;&#x8BA1;&#x5B66;&#x751F;&#x4EBA;&#x6570;&#x8D85;&#x8FC7;1&#x4EBA;&#x7684;&#x7701;&#x4EFD;<pre><code class="lang-sql"><span class="hljs-keyword">select</span> province,<span class="hljs-keyword">COUNT</span>(*) <span class="hljs-keyword">from</span> student <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> province <span class="hljs-keyword">having</span> <span class="hljs-keyword">COUNT</span>(*)&gt;<span class="hljs-number">1</span>
</code></pre>
</li>
<li>&#x5F97;&#x5230;&#x5B66;&#x751F;&#x7684;&#x5E74;&#x9F84;<pre><code class="lang-sql"><span class="hljs-keyword">select</span> <span class="hljs-keyword">DATEDIFF</span>(dd,birthday,<span class="hljs-keyword">GETDATE</span>())/<span class="hljs-number">365</span> <span class="hljs-keyword">from</span> student
</code></pre>
</li>
<li>&#x4E0D;&#x53CA;&#x683C;&#x6B21;&#x6570;&#x5927;&#x4E8E;1&#x6B21;&#x7684;&#x5B66;&#x751F;<pre><code class="lang-sql"><span class="hljs-keyword">select</span> student_id,<span class="hljs-keyword">COUNT</span>(*) &#x4E0D;&#x53CA;&#x683C;&#x6B21;&#x6570; <span class="hljs-keyword">from</span> score <span class="hljs-keyword">where</span> grade &lt;<span class="hljs-number">60</span> <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> student_id <span class="hljs-keyword">having</span> <span class="hljs-keyword">COUNT</span>(*)&gt;<span class="hljs-number">1</span>
</code></pre>
</li>
</ul>
<h2 id="t123. &#x5B50;&#x67E5;&#x8BE2;">3. &#x5B50;&#x67E5;&#x8BE2; <a href="#t123. &#x5B50;&#x67E5;&#x8BE2;"> # </a></h2>
<ul>
<li>&#x5B50;&#x67E5;&#x8BE2;&#x5C31;&#x662F;&#x6307;&#x51FA;&#x73B0;&#x5728;&#x5176;&#x5B83;SQL&#x8BED;&#x53E5;&#x4E2D;&#x7684;SELECT&#x8BED;&#x53E5;</li>
<li>Outer Query/ Inner Query</li>
<li>&#x5B50;&#x67E5;&#x8BE2;&#x6307;&#x5D4C;&#x5957;&#x5728;&#x67E5;&#x8BE2;&#x5185;&#x90E8;&#xFF0C;&#x4E14;&#x5FC5;&#x987B;&#x59CB;&#x7EC8;&#x51FA;&#x73B0;&#x5728;&#x5706;&#x62EC;&#x53F7;&#x4E2D;</li>
<li>&#x5B50;&#x67E5;&#x8BE2;&#x53EF;&#x4EE5;&#x5305;&#x542B;&#x591A;&#x4E2A;&#x5173;&#x952E;&#x5B57;&#x6216;&#x6761;&#x4EF6;</li>
<li>&#x5B50;&#x67E5;&#x8BE2;&#x7684;&#x5916;&#x5C42;&#x67E5;&#x8BE2;&#x53EF;&#x4EE5;&#x662F;: SELECT INSERT UPDATE SET&#x7B49;</li>
<li>&#x5B50;&#x67E5;&#x8BE2;&#x53EF;&#x4EE5;&#x8FD4;&#x56DE;&#x5E38;&#x91CF;&#x3001;&#x4E00;&#x884C;&#x6570;&#x636E;&#x3001;&#x4E00;&#x5217;&#x6570;&#x636E;&#x6216;&#x5176;&#x5B83;&#x5B50;&#x67E5;&#x8BE2;</li>
</ul>
<h3 id="t133.1  &#x6BD4;&#x8F83;&#x8FD0;&#x7B97;&#x7B26;&#x7684;&#x5B50;&#x67E5;&#x8BE2;">3.1  &#x6BD4;&#x8F83;&#x8FD0;&#x7B97;&#x7B26;&#x7684;&#x5B50;&#x67E5;&#x8BE2; <a href="#t133.1  &#x6BD4;&#x8F83;&#x8FD0;&#x7B97;&#x7B26;&#x7684;&#x5B50;&#x67E5;&#x8BE2;"> # </a></h3>
<ul>
<li>= &#x7B49;&#x4E8E;</li>
<li><blockquote>
<p>&#x5927;&#x4E8E;</p>
</blockquote>
</li>
<li>&lt; &#x5C0F;&#x4E8E;</li>
<li><blockquote>
<p>= &#x5927;&#x4E8E;&#x7B49;&#x4E8E;</p>
</blockquote>
</li>
<li>&lt;= &#x5C0F;&#x4E8E;&#x7B49;&#x4E8E;</li>
<li>&lt;&gt; &#x4E0D;&#x7B49;&#x4E8E;</li>
<li>!= &#x4E0D;&#x7B49;&#x4E8E;</li>
<li>&lt;=&gt; &#x5B89;&#x5168;&#x4E0D;&#x7B49;&#x4E8E;</li>
</ul>
<h3 id="t143.2 &#x67E5;&#x8BE2;&#x5E74;&#x9F84;&#x5927;&#x4E8E;&#x5E73;&#x5747;&#x5E74;&#x9F84;&#x7684;&#x5B66;&#x751F;">3.2 &#x67E5;&#x8BE2;&#x5E74;&#x9F84;&#x5927;&#x4E8E;&#x5E73;&#x5747;&#x5E74;&#x9F84;&#x7684;&#x5B66;&#x751F; <a href="#t143.2 &#x67E5;&#x8BE2;&#x5E74;&#x9F84;&#x5927;&#x4E8E;&#x5E73;&#x5747;&#x5E74;&#x9F84;&#x7684;&#x5B66;&#x751F;"> # </a></h3>
<pre><code class="lang-sql"><span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">ROUND</span>(<span class="hljs-keyword">AVG</span>(age),<span class="hljs-number">2</span>) <span class="hljs-keyword">FROM</span> student; 

<span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">from</span> student <span class="hljs-keyword">WHERE</span> age &gt; (<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">ROUND</span>(<span class="hljs-keyword">AVG</span>(age),<span class="hljs-number">2</span>) <span class="hljs-keyword">FROM</span> student)
</code></pre>
<h3 id="t153.2 ANY SOME ALL">3.2 ANY SOME ALL <a href="#t153.2 ANY SOME ALL"> # </a></h3>
<ul>
<li><blockquote>
<blockquote>
<p>= &lt; &lt;= = &lt;&gt; !=</p>
</blockquote>
</blockquote>
</li>
<li>ANY &#x4EFB;&#x4F55;&#x4E00;&#x4E2A;</li>
<li>SOME &#x67D0;&#x4E9B;</li>
<li>ALL &#x5168;&#x90E8;</li>
</ul>
<pre><code class="lang-sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">from</span> student <span class="hljs-keyword">WHERE</span> age &gt; <span class="hljs-keyword">ANY</span> (<span class="hljs-keyword">SELECT</span> age  <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">WHERE</span> province = <span class="hljs-string">&apos;&#x9655;&#x897F;&#x7701;&apos;</span>);
<span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">from</span> student <span class="hljs-keyword">WHERE</span> age &gt; <span class="hljs-keyword">SOME</span> (<span class="hljs-keyword">SELECT</span> age  <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">WHERE</span> province = <span class="hljs-string">&apos;&#x9655;&#x897F;&#x7701;&apos;</span>);
<span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">from</span> student <span class="hljs-keyword">WHERE</span> age &gt; ALL (<span class="hljs-keyword">SELECT</span> age  <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">WHERE</span> province = <span class="hljs-string">&apos;&#x9655;&#x897F;&#x7701;&apos;</span>);

<span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">from</span> student <span class="hljs-keyword">WHERE</span> age = <span class="hljs-keyword">ANY</span> (<span class="hljs-keyword">SELECT</span> age  <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">WHERE</span> province = <span class="hljs-string">&apos;&#x9655;&#x897F;&#x7701;&apos;</span>)
<span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">from</span> student <span class="hljs-keyword">WHERE</span> age = <span class="hljs-keyword">SOME</span> (<span class="hljs-keyword">SELECT</span> age  <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">WHERE</span> province = <span class="hljs-string">&apos;&#x9655;&#x897F;&#x7701;&apos;</span>)
</code></pre>
<h3 id="t163.3 &#x67E5;&#x8BE2;&#x4E00;&#x4E0B;&#x6709;&#x8003;&#x8BD5;&#x6210;&#x7EE9;&#x7684;&#x5B66;&#x751F;&#x4FE1;&#x606F;">3.3 &#x67E5;&#x8BE2;&#x4E00;&#x4E0B;&#x6709;&#x8003;&#x8BD5;&#x6210;&#x7EE9;&#x7684;&#x5B66;&#x751F;&#x4FE1;&#x606F; <a href="#t163.3 &#x67E5;&#x8BE2;&#x4E00;&#x4E0B;&#x6709;&#x8003;&#x8BD5;&#x6210;&#x7EE9;&#x7684;&#x5B66;&#x751F;&#x4FE1;&#x606F;"> # </a></h3>
<ul>
<li>[IN]</li>
<li>[NOT IN]</li>
</ul>
<pre><code class="lang-sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">where</span> <span class="hljs-keyword">id</span> <span class="hljs-keyword">in</span> (<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">distinct</span> student_id <span class="hljs-keyword">from</span> score);
</code></pre>
<ul>
<li>[EXISTS] </li>
<li>[NOTEXISTS]<pre><code class="lang-sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">where</span> <span class="hljs-keyword">EXISTS</span> (<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">distinct</span> student_id <span class="hljs-keyword">from</span> score <span class="hljs-keyword">where</span> student.id = score.student_id  )
</code></pre>
</li>
</ul>
<h2 id="t174. &#x8868;&#x8FDE;&#x63A5;">4. &#x8868;&#x8FDE;&#x63A5; <a href="#t174. &#x8868;&#x8FDE;&#x63A5;"> # </a></h2>
<h3 id="t18&#xFF14;.1&#x3000;&#x8FDE;&#x63A5;&#x7C7B;&#x578B;">&#xFF14;.1&#x3000;&#x8FDE;&#x63A5;&#x7C7B;&#x578B; <a href="#t18&#xFF14;.1&#x3000;&#x8FDE;&#x63A5;&#x7C7B;&#x578B;"> # </a></h3>
<ul>
<li>JOIN=INNER JOIN=CROSS JOIN &#x5185;&#x8FDE;&#x63A5;</li>
<li>LEFT [OUTER] JOIN &#x5DE6;&#x5916;&#x8FDE;&#x63A5;</li>
<li>RIGHT [OUTER] JOIN &#x53F3;&#x5916;&#x8FDE;&#x63A5;</li>
<li>ON &#x8FDE;&#x63A5;&#x6761;&#x4EF6;</li>
</ul>
<p><img src="http://img.zhufengpeixun.cn/tablejoin.jpg" alt="tablejoin"></p>
<h3 id="t19&#xFF14;.2&#x3000;&#x8FDE;&#x63A5;&#x6761;&#x4EF6;">&#xFF14;.2&#x3000;&#x8FDE;&#x63A5;&#x6761;&#x4EF6; <a href="#t19&#xFF14;.2&#x3000;&#x8FDE;&#x63A5;&#x6761;&#x4EF6;"> # </a></h3>
<p>&#x4F7F;&#x7528;ON&#x5173;&#x952E;&#x5B57;&#x6765;&#x8BBE;&#x5B9A;&#x8FDE;&#x63A5;&#x6761;&#x4EF6;&#xFF0C;&#x4E5F;&#x53EF;&#x4EE5;&#x4F7F;&#x7528;WHERE&#x6765;&#x4EE3;&#x66FF;</p>
<ul>
<li>ON&#x6765;&#x8BBE;&#x5B9A;&#x8FDE;&#x63A5;&#x6761;&#x4EF6;</li>
<li>&#x4E5F;&#x53EF;&#x4EE5;&#x4F7F;&#x7528;WHERE&#x6765;&#x5BF9;&#x7ED3;&#x679C;&#x8FDB;&#x884C;&#x8FC7;&#x6EE4;</li>
</ul>
<h3 id="t20&#xFF14;.3&#x3000;&#x5185;&#x8FDE;&#x63A5;">&#xFF14;.3&#x3000;&#x5185;&#x8FDE;&#x63A5; <a href="#t20&#xFF14;.3&#x3000;&#x5185;&#x8FDE;&#x63A5;"> # </a></h3>
<p>&#x663E;&#x793A;&#x5DE6;&#x8868;&#x548C;&#x53F3;&#x8868;&#x4E2D;&#x7B26;&#x5408;&#x6761;&#x4EF6;&#x7684;</p>
<pre><code class="lang-sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">INNER</span> <span class="hljs-keyword">JOIN</span> score <span class="hljs-keyword">ON</span> student.id = score.student_id;
</code></pre>
<h3 id="t21&#xFF14;.4&#x3000;&#x5DE6;&#x5916;&#x8FDE;&#x63A5;">&#xFF14;.4&#x3000;&#x5DE6;&#x5916;&#x8FDE;&#x63A5; <a href="#t21&#xFF14;.4&#x3000;&#x5DE6;&#x5916;&#x8FDE;&#x63A5;"> # </a></h3>
<p>&#x663E;&#x793A;&#x5DE6;&#x8868;&#x7684;&#x5168;&#x90E8;&#x548C;&#x53F3;&#x8868;&#x7B26;&#x5408;&#x6761;&#x4EF6;&#x7684;</p>
<pre><code class="lang-sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span> score <span class="hljs-keyword">ON</span> student.id = score.student_id;
</code></pre>
<h3 id="t22&#xFF14;.5&#x3000;&#x53F3;&#x5916;&#x8FDE;&#x63A5;">&#xFF14;.5&#x3000;&#x53F3;&#x5916;&#x8FDE;&#x63A5; <a href="#t22&#xFF14;.5&#x3000;&#x53F3;&#x5916;&#x8FDE;&#x63A5;"> # </a></h3>
<p>&#x663E;&#x793A;&#x53F3;&#x8868;&#x7684;&#x5168;&#x90E8;&#x548C;&#x5DE6;&#x8868;&#x7B26;&#x5408;&#x6761;&#x4EF6;&#x7684;</p>
<pre><code class="lang-sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">RIGHT</span> <span class="hljs-keyword">JOIN</span> score <span class="hljs-keyword">ON</span> student.id = score.student_id;
</code></pre>
<h3 id="t23&#xFF14;.6&#x3000;&#x66F4;&#x591A;&#x8868;&#x8FDE;&#x63A5;">&#xFF14;.6&#x3000;&#x66F4;&#x591A;&#x8868;&#x8FDE;&#x63A5; <a href="#t23&#xFF14;.6&#x3000;&#x66F4;&#x591A;&#x8868;&#x8FDE;&#x63A5;"> # </a></h3>
<pre><code class="lang-sql"><span class="hljs-keyword">SELECT</span> student.name,course.name,score.grade <span class="hljs-keyword">FROM</span> score 
<span class="hljs-keyword">INNER</span> <span class="hljs-keyword">JOIN</span> student <span class="hljs-keyword">ON</span> student.id = score.student_id
<span class="hljs-keyword">INNER</span> <span class="hljs-keyword">JOIN</span> course <span class="hljs-keyword">ON</span> course.id = score.course_id;
</code></pre>
<h3 id="t24&#xFF14;.7&#x3000;&#x65E0;&#x9650;&#x5206;&#x7C7B;[&#x81EA;&#x8EAB;&#x8FDE;&#x63A5;]">&#xFF14;.7&#x3000;&#x65E0;&#x9650;&#x5206;&#x7C7B;[&#x81EA;&#x8EAB;&#x8FDE;&#x63A5;] <a href="#t24&#xFF14;.7&#x3000;&#x65E0;&#x9650;&#x5206;&#x7C7B;[&#x81EA;&#x8EAB;&#x8FDE;&#x63A5;]"> # </a></h3>
<pre><code class="lang-sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">category</span>(
 <span class="hljs-keyword">id</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) PRIMARY <span class="hljs-keyword">KEY</span> AUTO_INCREMENT <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,
 <span class="hljs-keyword">name</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">50</span>),
 parent_id <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>)
)
</code></pre>
<pre><code class="lang-sql"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">category</span>(<span class="hljs-keyword">id</span>,<span class="hljs-keyword">name</span>,parent_id)
<span class="hljs-keyword">VALUES</span> (<span class="hljs-number">1</span>,<span class="hljs-string">&apos;&#x6570;&#x7801;&#x4EA7;&#x54C1;&apos;</span>,<span class="hljs-number">0</span>),(<span class="hljs-number">2</span>,<span class="hljs-string">&apos;&#x670D;&#x88C5;&apos;</span>,<span class="hljs-number">0</span>),(<span class="hljs-number">3</span>,<span class="hljs-string">&apos;&#x98DF;&#x54C1;&apos;</span>,<span class="hljs-number">0</span>),
(<span class="hljs-number">4</span>,<span class="hljs-string">&apos;iPad&apos;</span>,<span class="hljs-number">1</span>),(<span class="hljs-number">5</span>,<span class="hljs-string">&apos;&#x674E;&#x5B81;&apos;</span>,<span class="hljs-number">2</span>),(<span class="hljs-number">6</span>,<span class="hljs-string">&apos;&#x5EB7;&#x5E08;&#x5085;&apos;</span>,<span class="hljs-number">3</span>);
</code></pre>
<pre><code class="lang-sql"><span class="hljs-keyword">SELECT</span> c1.id,c1.name,<span class="hljs-keyword">COUNT</span>(<span class="hljs-number">1</span>) 
<span class="hljs-keyword">FROM</span> <span class="hljs-keyword">category</span> c1 <span class="hljs-keyword">INNER</span> <span class="hljs-keyword">JOIN</span> <span class="hljs-keyword">category</span> c2 <span class="hljs-keyword">ON</span> c1.id = c2.parent_id
<span class="hljs-keyword">WHERE</span> c1.parent_id = <span class="hljs-number">0</span>
<span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> c1.id;
</code></pre>
<p>&#x7236;&#x7C7B;&#x53D8;&#x6210;&#x540D;&#x79F0;</p>
<pre><code class="lang-sql"><span class="hljs-keyword">SELECT</span> c1.id,c1.name,p.name
<span class="hljs-keyword">FROM</span> <span class="hljs-keyword">category</span> c1 <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span> <span class="hljs-keyword">category</span> p <span class="hljs-keyword">ON</span> c1.parent_id = p.id
</code></pre>
<h3 id="t25&#xFF14;.8 &#x5220;&#x9664;&#x91CD;&#x590D;&#x8BB0;&#x5F55;[&#x591A;&#x8868;&#x5220;&#x9664;]">&#xFF14;.8 &#x5220;&#x9664;&#x91CD;&#x590D;&#x8BB0;&#x5F55;[&#x591A;&#x8868;&#x5220;&#x9664;] <a href="#t25&#xFF14;.8 &#x5220;&#x9664;&#x91CD;&#x590D;&#x8BB0;&#x5F55;[&#x591A;&#x8868;&#x5220;&#x9664;]"> # </a></h3>
<pre><code class="lang-sql"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">category</span>(<span class="hljs-keyword">id</span>,<span class="hljs-keyword">name</span>,parent_id)
<span class="hljs-keyword">VALUES</span>
(<span class="hljs-number">7</span>,<span class="hljs-string">&apos;iPad&apos;</span>,<span class="hljs-number">1</span>),
(<span class="hljs-number">8</span>,<span class="hljs-string">&apos;&#x674E;&#x5B81;&apos;</span>,<span class="hljs-number">2</span>),
(<span class="hljs-number">9</span>,<span class="hljs-string">&apos;&#x5EB7;&#x5E08;&#x5085;&apos;</span>,<span class="hljs-number">3</span>);
</code></pre>
<h4 id="t26&#xFF14;.8.1 &#x591A;&#x8868;&#x8054;&#x5408;&#x5220;&#x9664;&#x91CD;&#x590D;&#x8BB0;&#x5F55;">&#xFF14;.8.1 &#x591A;&#x8868;&#x8054;&#x5408;&#x5220;&#x9664;&#x91CD;&#x590D;&#x8BB0;&#x5F55; <a href="#t26&#xFF14;.8.1 &#x591A;&#x8868;&#x8054;&#x5408;&#x5220;&#x9664;&#x91CD;&#x590D;&#x8BB0;&#x5F55;"> # </a></h4>
<pre><code class="lang-sql">
<span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> <span class="hljs-keyword">category</span> c1 <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span> 
(<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">id</span>,<span class="hljs-keyword">name</span> <span class="hljs-keyword">from</span> <span class="hljs-keyword">category</span> <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> <span class="hljs-keyword">name</span> <span class="hljs-keyword">HAVING</span> <span class="hljs-keyword">COUNT</span>(<span class="hljs-number">1</span>)&gt;<span class="hljs-number">1</span>) c2
<span class="hljs-keyword">ON</span> c1.name = c2.name <span class="hljs-keyword">WHERE</span> c1.id != c2.id
</code></pre>
<h4 id="t27&#xFF14;.8.2 IN NOT IN&#x5220;&#x9664;&#x91CD;&#x590D;&#x8BB0;&#x5F55;">&#xFF14;.8.2 IN NOT IN&#x5220;&#x9664;&#x91CD;&#x590D;&#x8BB0;&#x5F55; <a href="#t27&#xFF14;.8.2 IN NOT IN&#x5220;&#x9664;&#x91CD;&#x590D;&#x8BB0;&#x5F55;"> # </a></h4>
<pre><code class="lang-sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> <span class="hljs-keyword">category</span> c1 
<span class="hljs-keyword">WHERE</span> c1.name <span class="hljs-keyword">IN</span> 
(<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">name</span> <span class="hljs-keyword">from</span> <span class="hljs-keyword">category</span> <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> <span class="hljs-keyword">name</span> <span class="hljs-keyword">HAVING</span> <span class="hljs-keyword">COUNT</span>(<span class="hljs-number">1</span>)&gt;<span class="hljs-number">1</span>)
<span class="hljs-keyword">AND</span> c1.id <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">IN</span> 
(<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">MIN</span>(<span class="hljs-keyword">id</span>) <span class="hljs-keyword">from</span> <span class="hljs-keyword">category</span> <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> <span class="hljs-keyword">name</span> <span class="hljs-keyword">HAVING</span> <span class="hljs-keyword">COUNT</span>(<span class="hljs-number">1</span>)&gt;<span class="hljs-number">1</span>)
</code></pre>
<h3 id="t284.9 (&#x63D2;&#x5165;&#x7701;&#x4EFD;)INSERT SELECT">4.9 (&#x63D2;&#x5165;&#x7701;&#x4EFD;)INSERT SELECT <a href="#t284.9 (&#x63D2;&#x5165;&#x7701;&#x4EFD;)INSERT SELECT"> # </a></h3>
<pre><code class="lang-sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> province(<span class="hljs-keyword">id</span> <span class="hljs-built_in">int</span> PRIMARY <span class="hljs-keyword">KEY</span> AUTO_INCREMENT,<span class="hljs-keyword">name</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">50</span>))
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> province(<span class="hljs-keyword">name</span>) <span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">DISTINCT</span> province <span class="hljs-keyword">FROM</span> student;
</code></pre>
<h3 id="t29&#xFF14;.10&#x3000;&#x66F4;&#x65B0;&#x7701;&#x4EFD;(&#x591A;&#x8868;&#x66F4;&#x65B0;)">&#xFF14;.10&#x3000;&#x66F4;&#x65B0;&#x7701;&#x4EFD;(&#x591A;&#x8868;&#x66F4;&#x65B0;) <a href="#t29&#xFF14;.10&#x3000;&#x66F4;&#x65B0;&#x7701;&#x4EFD;(&#x591A;&#x8868;&#x66F4;&#x65B0;)"> # </a></h3>
<pre><code class="lang-sql"><span class="hljs-keyword">UPDATE</span> student <span class="hljs-keyword">INNER</span> <span class="hljs-keyword">JOIN</span> province <span class="hljs-keyword">ON</span> student.province=province.name
 <span class="hljs-keyword">SET</span> student.province=province.id 
<span class="hljs-keyword">WHERE</span> student.province =  province.name;
</code></pre>
<h3 id="t30&#xFF14;.11&#x3000;&#x591A;&#x8868;&#x8054;&#x5408;&#x67E5;&#x8BE2;">&#xFF14;.11&#x3000;&#x591A;&#x8868;&#x8054;&#x5408;&#x67E5;&#x8BE2; <a href="#t30&#xFF14;.11&#x3000;&#x591A;&#x8868;&#x8054;&#x5408;&#x67E5;&#x8BE2;"> # </a></h3>
<pre><code class="lang-sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">INNER</span> <span class="hljs-keyword">JOIN</span> province <span class="hljs-keyword">ON</span> student.province=province.id;
</code></pre>
<h3 id="t31&#xFF14;.12 CREATE SELECT">&#xFF14;.12 CREATE SELECT <a href="#t31&#xFF14;.12 CREATE SELECT"> # </a></h3>
<p>&#x521B;&#x5EFA;&#x8868;&#x7684;&#x540C;&#x65F6;&#x5C06;&#x7ED3;&#x679C;&#x5199;&#x5165;&#x5230;&#x6570;&#x636E;&#x8868;</p>
<pre><code class="lang-sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">table</span> city(<span class="hljs-keyword">id</span> <span class="hljs-built_in">int</span> AUTO_INCREMENT PRIMARY <span class="hljs-keyword">KEY</span>,<span class="hljs-keyword">name</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">50</span>) )
<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">DISTINCT</span> city <span class="hljs-keyword">name</span> <span class="hljs-keyword">from</span> student;

<span class="hljs-keyword">UPDATE</span> student <span class="hljs-keyword">INNER</span> <span class="hljs-keyword">JOIN</span> city <span class="hljs-keyword">on</span> student.city = city.name
<span class="hljs-keyword">SET</span> student.city = city.id
<span class="hljs-keyword">WHERE</span> student.city = city.name;
</code></pre>
<h3 id="t32&#xFF14;.13 &#x4FEE;&#x6539;&#x5217;&#x540D;&#x548C;&#x7C7B;&#x578B;">&#xFF14;.13 &#x4FEE;&#x6539;&#x5217;&#x540D;&#x548C;&#x7C7B;&#x578B; <a href="#t32&#xFF14;.13 &#x4FEE;&#x6539;&#x5217;&#x540D;&#x548C;&#x7C7B;&#x578B;"> # </a></h3>
<pre><code class="lang-sql"><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> student 
<span class="hljs-keyword">CHANGE</span> province province_id <span class="hljs-built_in">SMALLINT</span> <span class="hljs-keyword">UNSIGNED</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,
<span class="hljs-keyword">CHANGE</span> city city_id <span class="hljs-built_in">SMALLINT</span> <span class="hljs-keyword">UNSIGNED</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>;
</code></pre>

        <div class="copyright">Powered by <a href="https://github.com/jaywcjlove/idoc" target="_blank">idoc</a>. Dependence <a href="https://nodejs.org">Node.js</a> run.</div>
    </div>
    
</div>

<script src="https://cdn.bootcss.com/jquery/3.0.0/jquery.js"></script>
<script>
$('.warpper .page-toc ul ul li a').on('click',function(){
  $('.warpper .page-toc ul ul li a').removeClass('my-active')
  $(this).addClass('my-active')
})
  // if (!$('.understand-me').length) {
  //   var bar = $(window).height() - $('.navbar ').height() - $('.page-toc').position().top;
  //   var count = bar / 26 / 2;
  //   var barHeight = $('.page-toc').outerHeight();
  //   $('.page-toc li').eq(0).children('a').addClass('red');
  //   var arr = [];
  //   $("h1,h2,h3,h4,h5,h6").each(function () {
  //     arr.push($(this).position().top);
  //   });
  //   var timer
  //   function dark() {
  //     clearTimeout(timer)
  //      timer = setTimeout(function () {
  //      var top = Math.abs($('.page-toc > ul').position().top);
  //      var cur = $('.content').scrollTop();
  //      for (var i = arr.length; i >= 0; i--) {
  //        if (arr[i] <= cur) {
  //          break;
  //        }
  //      }
  //      if (i === -1) {
  //        i = 0;
  //      }
  //      $('.page-toc li a').removeClass('red');
  //      $('.page-toc li').eq(i).children('a').addClass('red');
  //      let height = $('.page-toc li').eq(i).position().top-$('.page-toc').height(); // 如果当前的offset出去了 回到中间可好？
  //      $('.page-toc').scrollTop(height+$('.page-toc').height()/2);
  //    },200)
  //   }

  //   $('.content').on('scroll', dark);
  // }
</script>
<style>

    /* ::-webkit-scrollbar{width:14px;}
    ::-webkit-scrollbar-track{background-color:transparent;}
    ::-webkit-scrollbar-thumb{background-color:transparent;}
    ::-webkit-scrollbar-thumb:hover {background-color:transparent}
    ::-webkit-scrollbar-thumb:active {background-color:transparent} */

    .page-toc > ul .red {
        background: #f3f3f3;
        z-index: 1;
        border-left: 3px solid #009a61;
        -webkit-transition: all .2s ease;
        transition: all .2s ease;
        color: #000
    }





</style>
</body>
</html>
